﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class KhachHangDAO
    {
        public static DataTable GetAll()
        {
            DataProvider dp = new DataProvider();
            string sql = "Select kh.MaKhachHang, kh.TenKhachHang, lk.MaLoaiKhach,lk.TenLoaiKhach, kh.CMND, kh.DiaChi From KhachHang kh, LoaiKhach lk where kh.MaLoaiKhach = lk.MaLoaiKhach";
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable GetTheoMa(int ma)
        {
            DataProvider dp = new DataProvider();
            string sql = "Select MaLoaiKhach From KhachHang where MaKhachHang = " + ma + "";
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable KhachTheoMa(int ma)
        {
            DataProvider dp = new DataProvider();
            string sql = "Select kh.MaKhachHang, kh.TenKhachHang, lk.TenLoaiKhach, kh.CMND, kh.DiaChi From KhachHang kh, LoaiKhach lk where kh.MaLoaiKhach = lk.MaLoaiKhach and kh.MaKhachHang = " + ma + "";
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable MaKhachTheoTen(string ten)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select MaKhachHang From KhachHang Where TenKhachHang like N'%" + ten + "%'");
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public void ThemKhach(KhachHangDTO kh)
        {
            string sql = string.Format("INSERT INTO KhachHang (MaKhachHang, TenKhachHang,MaLoaiKhach, CMND, DiaChi) VALUES (" + kh.MaKhachHang + ", '" + kh.TenKhachHang + "','" + kh.MaLoaiKhach + "','" + kh.Cmnd + "','" + kh.DiaChi + "')");
            DataProvider.ExecuteNonQuery(sql);
        }

        public void CapNhatKhach(KhachHangDTO kh)
        {
            string sql = string.Format("Update KhachHang Set TenKhachHang = '" + kh.TenKhachHang + "', CMND = " + kh.Cmnd + ", DiaChi = '" + kh.DiaChi + "', MaLoaiKhach = " + kh.MaLoaiKhach + " Where MaKhachHang = " + kh.MaKhachHang + "");
            DataProvider.ExecuteNonQuery(sql);
        }

        public void XoaKhach(int makhach)
        {
            string sql = string.Format("Delete From KhachHang Where MaKhachHang = " + makhach + "");
            DataProvider.ExecuteNonQuery(sql);
        }

        public static DataTable TimKhachHang(string tenkh)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select * From KhachHang Where TenKhachHang like N'%" + tenkh + "%'");
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable LayThongTinKhachHangTheoMa(int makh)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select * From KhachHang Where MaKhachHang = " + makh);
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }
    }
}
